package com.api.gbhg.liuwenbo.dao;

import com.api.gbhg.liwenjun.VersionFlowUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.TimeUtil;
import weaver.general.Util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DataBaseUtil {

    public static final Logger LOGGER = LoggerFactory.getLogger("gbhg");

    /**
     * 根据requestid获取节点id
     * @param requestid
     * @return
     */
    public static Integer getNodeid(String requestid){
        String sql = "select currentnodeid from workflow_requestbase where requestid='" + requestid + "'";
        RecordSet rss = new RecordSet();
        rss.executeQuery(sql);
        rss.next();
        int nodeid = Util.getIntValue(rss.getString(1));
        return nodeid;
    }

    /**
     * 根据requestid获取节点名称
     * @param requestid
     * @return
     */
    public static String getNodeName(String requestid){
        String sql = "select nodename from workflow_nodebase a left join workflow_requestbase b on a.id = b.currentnodeid where b.requestid='" + requestid + "'";
        RecordSet rss = new RecordSet();
        rss.executeQuery(sql);
        rss.next();
        String nodename = rss.getString(1);
        return nodename;
    }

    public static String getOSDataByrustId(String requestId,String dbType){
        StringBuilder sb = new StringBuilder();
        String sql=" SELECT t.id,v.lcjlsqjlid,v.xmmin,v.sbdw,v.sbks,v.fzr,v.yszje,t.zfcgyswy,t.hwgz, \n" +
                "t.gc,t.fw,t.xecg,SUM(t1.bzs) bzs,SUM(t1.xgyrs) xgyrs,SUM(t1.bzbhtgrs) bzbhtgrs \n" +
                "FROM uf_jingfei_version v join uf_bgypbgjfbb t on v.lcjlsqjlid = t.requestId \n" +
                "join uf_bgypbgjfbb_dt1 t1 on t.id = t1.mainid  ";
        sb.append(sql);

        if(!requestId.isEmpty()&&requestId!=null){
            sb.append(" where v.lcjlsqjlid in ("+requestId+")  ");
            sb.append(" group by v.Id,t.id,v.lcjlsqjlid,v.xmmin,v.sbdw,v.sbks,v.fzr,v.yszje,t.zfcgyswy,t.requestId,t.hwgz,t.gc,t.fw,t.xecg ");
            LOGGER.info(sb.toString());
            return sb.toString();
        }else{
            return null;
        }

    }


    /**
     * 权限处理
     * @param tableName
     * @param uuid
     * @param modeid
     */
    public static void setPower(String tableName,String uuid,Integer modeid){
        RecordSet rss = new RecordSet();
        rss.executeQuery("select id from " + tableName + " where modeuuid=?", uuid);
        if (rss.next()) {
            int idnew = Integer.parseInt(rss.getString(1));
            ModeRightInfo modeRightInfo = new ModeRightInfo();
            modeRightInfo.setNewRight(true);
            modeRightInfo.editModeDataShare(1, modeid, idnew);
        }
    }

    /**
     * 获取建模表id
     * @param tableName
     * @return
     */
    public static Integer getModeId(String tableName){
        String sql = "select id from modeinfo where formid=(select id from workflow_bill where tablename='" + tableName + "')";
        RecordSet rss = new RecordSet();
        rss.executeQuery(sql);
        rss.next();
        int modeid = Util.getIntValue(rss.getString(1));
        return modeid;
    }


    /**
     * 获取插入建模建模表insert 语句
     * @param tableName
     */
    public static String getModeInsertSql(List<String> tableFields, String tableName,String uuid,Integer modeid){

        String currentDateString = TimeUtil.getCurrentDateString();
        String currentTimeString = TimeUtil.getOnlyCurrentTimeString();

        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("insert into " + tableName + " (");
        stringBuffer.append(VersionFlowUtil.joinStringBySplit(tableFields, ",")+",modedatacreater,modedatacreatedate,modedatacreatertype,modedatacreatetime,formmodeid,modeuuid");
        stringBuffer.append(") values (");
        stringBuffer.append(VersionFlowUtil.getQuestionMark(tableFields.size())+",1"+",'"+currentDateString+"',"+"0"+",'"+currentTimeString+"',"+modeid+",'"+uuid+"'");
        stringBuffer.append(")");
        return stringBuffer.toString();
    }


    /**
     * 根据subcompanyId查询以下所有部门id
     * @param subcompanyId
     * @return
     */
    public static List<String> getSupSubcomIdBySubId(String subcompanyId){
        RecordSet rs = new RecordSet();
        String sql = " select id from HrmDepartment where subcompanyid1 in (select id from HrmSubCompany where supsubcomid = "+subcompanyId+") ";
        rs.executeQuery(sql);
        List<String> list = new ArrayList<>();
        while (rs.next()){
            list.add(rs.getString("id"));
        }
        list.add(subcompanyId);
        return list;
    }



    /**
     * 经费入库数据来源查询
     * @param requestId
     * @param tbName
     * @return
     */
    public static RecordSet getRs (String requestId, String tbName){
        RecordSet rs=new RecordSet();
        String sql = "SELECT jf.lcjlsqjlid,jf.lcbj,jf.lcjlsqjlid,dt2.bmyszcjjfl,dt2.dyzfjjfl,jf.ysnd,dt2.hjwy yszje,jf.sbdw,jf.xmmin,jf.jbr,jf.gkdw,cast(jf.sydw AS varchar(300)) as sydw FROM \n" +
                "uf_jingfei_version jf JOIN  "+tbName+" bg ON jf.requestId = bg.requestId\n" +
                "JOIN "+tbName+"_dt2 dt2 ON bg.id = dt2.mainid\n" +
                "WHERE jf.sfky = 1 AND jf.lcjlsqjlid = "+requestId+ "\n" +
                "GROUP BY dt2.bmyszcjjfl,dt2.dyzfjjfl,jf.lcbj,jf.lcid,jf.ysnd,jf.lcjlsqjlid,jf.sbdw,jf.xmmin,jf.jbr,dt2.hjwy,jf.gkdw,cast(jf.sydw AS varchar(300)) ";
        rs.executeQuery(sql);
        return rs;
    }

    /**
     * 获取核批数据总数量
     * @param lcztz
     * @param tableName
     * @return
     */
    public static int getDataCount(String lcztz,String tableName){
        RecordSet rs = new RecordSet();
        StringBuilder sb = new StringBuilder();
        String sql = "SELECT COUNT(1)AS num FROM uf_jingfei_version jf JOIN "+tableName+" v ON jf.lcjlsqjlid=v.requestId where ";
        sb.append(sql);
        try {
            if(lcztz.equals("3")){
                sb.append(" lcztz="+lcztz);
            }else {
                sb.append(" lcztz != 3");
            }
            rs.executeQuery(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        rs.next();
        return rs.getInt("num");
    }

    /**
     * 获取项目执行情况统计数据
     * @param pageNum 当前页
     * @param pageSize 当前数据量(条)
     * @param nd 年度
     * @param dw 单位
     * @param state 独立核算
     * @return
     */
    public static RecordSet getPjStRs(Integer pageNum,Integer pageSize,String nd,String dw,int state){
        RecordSet rs = new RecordSet();
        StringBuilder sb = new StringBuilder();
        if (dw.equals("2")){
            sb.append("select jf.xmmc,jf.sbdw,SUM(jf.ze) ze,SUM(jf.syed) syed,jf.requestId from " +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+" group by jf.requestId,jf.xmmc,jf.sbdw  ");
        }else{
            sb.append("select jf.xmmc,jf.sbdw,SUM(jf.ze) ze,SUM(jf.syed) syed,jf.requestId from " +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+" and jf.sbdw in ("+dw+") group by jf.requestId,jf.xmmc,jf.sbdw  ");
        }
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sb.append(" order by requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sb.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sb.toString());
        return rs;
    }

    /**
     * 预算执行率
     * @param nd 年度
     * @param sbdw 申报单位
     * @return
     */
    public static RecordSet getTotalImplement(String nd,String sbdw,int state){
        RecordSet rs = new RecordSet();
        String sql ;
        if (sbdw.equals("2")){
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+" ";
        }else{
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+" and jf.sbdw in ("+sbdw+") ";
        }

        rs.executeQuery(sql);
        return rs;
    }

    /**
     * 拨款
     * @param nd 年度
     * @param sbdw 申报单位
     * @param ysly 预算类型
     * @param state 核算类型
     * @return
     */
    public static RecordSet getAppropriationData(String nd,String sbdw,String ysly,int state){
        RecordSet rs = new RecordSet();
        String sql ;
        if (sbdw.equals("2")){
            sql = "select  SUM(je) je,SUM(syed) syed  from uf_jingfeilaiyuan jfly join uf_jingfei_version jv on jfly.requestId = jv.requestId \n" +
                    "where jfly.nd = "+nd+"  and jfly.ysly = "+ysly+" and jv.sfwdlhsdw = "+state+" ";
        }else{
            sql = "select  SUM(je) je,SUM(syed) syed  from uf_jingfeilaiyuan jfly join uf_jingfei_version jv on jfly.requestId = jv.requestId \n" +
                    "where jfly.nd = "+nd+"  and jfly.ysly = "+ysly+" and jv.sfwdlhsdw = "+state+" and jfly.sbdw in ("+sbdw+") ";
        }

        rs.executeQuery(sql);
        return rs;
    }

    /**
     * 项目执行统计总数量
     * @param nd 年度
     * @param dw 单位
     * @param state 是否独立核算
     * @return
     */
    public static int getPjStCount(String nd,String dw,int state){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.equals("2")){
            sql = "select COUNT(requestId) cut from \n" +
                    "(select jf.requestId from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where  jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+"  group by jf.requestId ) tb  ";
        }else{
            sql = "select COUNT(requestId) cut from \n" +
                    "(select jf.requestId from uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    "where  jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+" and jf.sbdw in ("+dw+") group by jf.requestId ) tb  ";
        }
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("cut");
    }


    /**
     * 核批任务
     * @param nd 预算年度
     * @param state 是否未独立单位 独立单位：1 则 0
     * @return
     */
    public static Map<String,Integer> getApprovalTask(int nd,int state,String dw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.equals("2")){
            sql = "select (select count(id) cut from uf_jingfei_version where sfwdlhsdw = "+state+" and lcztz = 3 and ysnd = "+nd+") processed,count(id) unprocessed \n" +
                    "from  uf_jingfei_version where sfwdlhsdw ="+state+" and lcztz != 3 and ysnd = "+nd+"; ";
        }else{
            sql = "select (select count(id) cut from uf_jingfei_version where sfwdlhsdw = "+state+" and lcztz = 3 and ysnd = "+nd+" and sbdw in ("+dw+")) processed,count(id) unprocessed \n" +
                    "from  uf_jingfei_version where sfwdlhsdw ="+state+" and lcztz != 3 and ysnd = "+nd+" and sbdw in ("+dw+")   ; ";
        }

        rs.executeQuery(sql);
        rs.next();
        Map<String,Integer> map = new HashMap<>();
        map.put("processed",rs.getInt("processed")); //已处理
        map.put("unprocessed",rs.getInt("unprocessed")); //未处理
        return map;
    }

    /**
     * 预算执行
     * @param nd 年度
     * @param state 是否独立核算
     * @param dw 单位
     * @return
     */
    public static RecordSet getBudgetImplementation(int nd,int state,String dw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.equals("2")){
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from \n" +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+"  ";
        }else{
            sql = "select SUM(jf.ze) ze,SUM(jf.syed) syed from \n" +
                    " uf_jingfei jf join uf_jingfei_version jv on jf.requestId = jv.requestId \n" +
                    " where jf.nd = "+nd+" and jv.sfwdlhsdw = "+state+" and jf.sbdw in ("+dw+")  ";
        }

        rs.executeQuery(sql);
        rs.next();
        return rs;
    }

    /**
     * 根据部门id查找分部id
     * @param DptId
     * @return
     */
    public static int getSubComIdByDptId(Integer DptId){
        RecordSet rs = new RecordSet();
        String sql = "select subcompanyid1 from HrmDepartment where id = "+DptId+"; ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("subcompanyid1");
    }

    /**
     *  根据上级分部id查询同级
     * @param supId 上级分部id
     */
    public static RecordSet getSubComIdBySupId(Integer supId){
        RecordSet rs = new RecordSet();
        String sql = "select id,subcompanyname from HrmSubCompany where supsubcomid = "+supId+"; ";
        rs.executeQuery(sql);
        return rs;
    }

    /**
     * 获取预算核批数据
     * @param pageNum 当前页
     * @param pageSize 页面数量
     * @param nd 年度
     * @param dw 单位
     * @param state 待办 false 已办 false
     * @return
     */
    public static RecordSet getBudAppData(Integer pageNum,Integer pageSize,int nd,String dw,boolean state,Integer hsdw){
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (state){
            if(dw.equals("2"))
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+" and lcztz = 3 and sfwdlhsdw = "+hsdw+" ");
            else
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+" and sbdw in ("+dw+") and lcztz = 3 and sfwdlhsdw = "+hsdw+" ");
        }else{
            if(dw.equals("2"))
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+"  and lcztz != 3 and sfwdlhsdw = "+hsdw+" ");
            else
                sql.append("select  xmmin,yszje,cjsj from uf_jingfei_version where ysnd = "+nd+" and sbdw in ("+dw+")  and lcztz != 3 and sfwdlhsdw = "+hsdw+" ");
        }
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sql.append(" order by requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sql.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 获取预算核批数据总条数
     * @param nd
     * @param dw
     * @param state
     * @param hsdw
     * @return
     */
    public static int getBudAppCount(int nd,String dw,boolean state,Integer hsdw){
        RecordSet rs = new RecordSet();
        String sql ;
        if (state) {
            if (dw.equals("2"))
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and lcztz = 3 and sfwdlhsdw = " + hsdw + " ";
            else
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and sbdw in (" + dw + ") and lcztz = 3 and sfwdlhsdw = " + hsdw + " ";
        }else{
            if (dw.equals("2"))
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and lcztz != 3 and sfwdlhsdw = " + hsdw + " ";
            else
                sql = "select count(requestId) cut from uf_jingfei_version where ysnd = " + nd + " and sbdw in (" + dw + ") and lcztz != 3 and sfwdlhsdw = " + hsdw + " ";
        }
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("cut");
    }

    /**
     * 根据用户id获取部门id
     * @param id
     * @return
     */
    public static String getDepartmentIdById(Integer id){
        RecordSet rs = new RecordSet();
        String sql = " select departmentid from hrmResource where id = "+id+" ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("departmentid");
    }

    /**
     * 根据用户id获取授权列表授权的单位
     * @param id
     * @return
     */
    public static String getAuthoritySubIdByUid(String id){
        RecordSet rs = new RecordSet();
        String sql = " select * from \n" +
                "(SELECT subcomid,value\n" +
                "FROM(\n" +
                "  SELECT subcomid, ysbzjbr = CONVERT(xml,'<root><v>' + REPLACE(convert(varchar,ysbzjbr), ',', '</v><v>') + '</v></root>') FROM HrmSubcompanyDefined\n" +
                ")A\n" +
                "OUTER APPLY(\n" +
                "  SELECT value = N.v.value('.', 'varchar(100)') FROM A.ysbzjbr.nodes('/root/v') N(v)\n" +
                ")B\n" +
                ")HrmSubcompanyDefined2 where value =  "+id+" ";
        rs.executeQuery(sql);
        StringBuilder sbi = new StringBuilder();
        while (rs.next()){
            sbi.append(rs.getString("subcomid")+",");
        }
        return sbi.substring(0,sbi.length() -1);
    }

    /**
     * 判断用户是否存在授权列表
     * @param id
     * @return
     */
    public static boolean isAuthorityByUid(String id){
        RecordSet rs = new RecordSet();
        String sql = " select * from \n" +
                "(SELECT subcomid,value\n" +
                "FROM(\n" +
                "  SELECT subcomid, ysbzjbr = CONVERT(xml,'<root><v>' + REPLACE(convert(varchar,ysbzjbr), ',', '</v><v>') + '</v></root>') FROM HrmSubcompanyDefined\n" +
                ")A\n" +
                "OUTER APPLY(\n" +
                "  SELECT value = N.v.value('.', 'varchar(100)') FROM A.ysbzjbr.nodes('/root/v') N(v)\n" +
                ")B\n" +
                ")HrmSubcompanyDefined2 where value = "+id+" ";
        LOGGER.info("isAuthorityByUid>>"+sql);
        rs.executeQuery(sql);
        if (rs.getCounts()>0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 根据流程id获取流程表名
     * @param workflowId
     * @return
     */
    public static String getWorkflowTabNameByWid(String workflowId){
        RecordSet rs = new RecordSet();
        String sql = "select tablename from workflow_bill where id = (select formid from workflow_base where id = "+workflowId+")";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("tablename");
    }

    /**
     * 查询单位名称
     * @param id
     * @return
     */
    public static String getSubcompanyNameById(String id){
        RecordSet rts=new RecordSet();
        String sql="select SUBCOMPANYNAME from hrmsubcompany where id="+id;
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("SUBCOMPANYNAME");
    }

    /**
     * 获取拱北海关
     * @return
     */
    public static String getGonBeiSubCompany(){
        RecordSet rs = new RecordSet();
        String sql = "select id from HrmSubCompany";
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("id");
    }

}
